﻿Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.IO
Public Class ImportFromExcel
    Dim RTable As System.Data.DataTable
    Public Sub New(ByVal TableStruct As System.Data.DataTable)
        TableStruct.Rows.Clear()
        RTable = TableStruct
    End Sub
    Public Sub ReadFile(ByVal FileName As String)
        If RTable Is Nothing Then Throw New Exception("未准备接收数据的结构表！")
        If File.Exists(FileName) = False Then Throw New Exception("文件不存在，请重新选择数据文件！")
        Dim app As New Excel.Application
        Dim wb As Workbook = Nothing
        Dim ReadSheet As Worksheet
        Dim HasError As Boolean = False
        Dim ErrTxt As String = ""
        Try
            wb = app.Workbooks.Open(FileName)
            ReadSheet = wb.Sheets(1)
            Dim RowCount As Integer = ReadSheet.UsedRange.Rows.Count
            Dim ColCount As Integer = ReadSheet.UsedRange.Columns.Count
            For i = 1 To RowCount
                Dim DataRow As DataRow = RTable.NewRow
                Try
                    DataRow.Item(0) = i
                    For j = 1 To ColCount
                        DataRow.Item(j) = ReadSheet.Cells(i, j).value
                    Next
                    RTable.Rows.Add(DataRow)
                Catch ex As Exception
                    If ErrTxt <> "" Then
                        ErrTxt += vbCrLf & "行：" & i & "【" & ex.Message & "】"
                    Else
                        ErrTxt = "行：" & i & "【" & ex.Message & "】"
                    End If
                    HasError = True
                End Try
            Next
            wb.Close()
            app.Quit()
            If HasError Then MsgBox("导入结束，导入过程中有错误记录，请注意检查数据！" & vbCrLf & ErrTxt, MsgBoxStyle.Information, "提示")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "读取错误")
            If wb IsNot Nothing Then wb.Close()
            app.Quit()
        End Try
    End Sub
End Class
